DATABASE MANAGEMENT

SYSTEM

THEORY AND IDEAS

25-04-2019 1

25-04-2019 2

Information is not useful if not organized

In database, data are organized in a way that people find meaningful

and useful.

Database Management System (DBMS) is used to input, sort, organize

and store data.

A collection of related data. It should support

Definition

Construction

Manipulation

Database Management System: A collection of programs that enable the

users to create and maintain a database

DATABASE MANAGEMENT SYSTEM

DATABASE

MANAGEME

NT SYSTEM

4/25/2019 3

DATABASE

MANAGEME

NT SYSTEM

4/25/2019 4

DATABASE

MANAGEMENT SYSTEM

• Data storage, retrieval, and update: The

ability to store, retrieve, and update the

data that are in the database.

• User-accessible catalog: where

descriptions of database components are

stored and are accessible to the users

• Shared update support: A mechanism to

ensure accuracy when several users are

updating the database at the same time

• Backup and Recovery Services:

Mechanisms for recovering the database

in the event that a database is damaged

somehow.

• Security Services: Mechanisms to ensure

that certain rules are followed with regard

to data in the database and any changes

that are made in the data

4/25/2019 5

DATABASE

MANAGEMENT

SYSTEM

5. Integrity services: Mechanisms to

ensure that certain rules are

followed with regard to data in the

database and any changes that are

made in the data.

6. Data Independence: Facilities to

support the independence of

programs from the structure of the

database.

7. Replication support: A facility to

manage copies of the same data at

multiple locations.

8. Utility Services: DBMS provided

services that assist in the general

maintenance of the database.

• SHARED UPDATES

• Multiple users are making updates to the

database at the same time.

Problem:

• Multiple people updating the

database simultaneously can override

each other

Example:

• Agents T1 & T2 simultaneously read

the seats reserved on Flight 890 i.e.

• T1 cancels 5 seats updating the seats

reserved on Flight 890 to 75

• T2 reserves 4 additional seats on the

flight and updates the seats reserved

on Flight 890 to 84.

• If T1 updates the database before T2.

T2 will override T1’s change and make

reservations to 84 rather than getting

the correct value of 79.

• Similarly if T2 updates before T1 the

seats reserved will be 75

• SHARED UPDATES SOLUTION : Batch processing |

Locking

DATABASE

MANAGEMENT SYSTEM

• Two phase lock ---

• Required when multiple records are updated as a

result of a user action (e.g. filling form etc.)

• All the records accessed are locked progressively

till the required updates are completed

• Growing Phase: More and more locks are

added without releasing locks

• After all locks are placed the database is

updated

• Shrinking Phase: All locks are removed and

no new ones are added

DATABASE

MANAGEMENT

SYSTEM

4/25/2019 8

DATABASE

MANAGEMENT

SYSTEM

4/25/2019 9

Security

• Protection against unauthorized access: either

intentional or accidental.

• Three main features for protection

• Passwords: Allows only authorized users to access the

database. Access privileges can be provided based on

access needs

• Encryption: Encodes data to non-decipherable. Data

decoded on demand to prevent hackers from accessing

data

• Views: Different snapshot of the data ensures that users

only get access to data they need

Integrity

• Integrity Constraints are the conditions that data

must satisfy during initial input & updates.

• There are four categories of constraints

• Data Type

• Legal Values

• Format

• Key Constraints

• Entity Integrity Constraints (Primary Key)

• Enforces the uniqueness of the primary key

• Referential Integrity Constraints (Foreign Key)

• Value of foreign key must match the value of primary key for some row

in another table

Integrity: Solutions

• Ignore constraint

• Undesirable as it can lead to inconsistent data

• Let user enforce the constraint

• Undesirable since user mistakes can be disastrous

• Let programmer build the logic of constraints in

the programs

• Makes programs complex: harder to write, harder to

maintain, and expensive

• Place burden on the DBMS.

• Preferred way: Cost of DBMS development amortized

over large user base, hence economical

Replication

• Duplication of data at multiple physical locations

• Each replica of the data can be changed

independently

• Periodically the replicas update their data to the

master database – this process is called

synchronization

Disaster Planning: Backup & Recovery

• Database can be damaged in a number of ways

• Power outage, disk crashes, floods, user errors

• Periodic backups limit the loss due to sudden failures

• Data can be recovered from the latest backup and

the changes since the backup need to be done in

either of two ways

• Manually

• From a catalog (if exists) recording all updates to the

database since the last backup.

Catalog/Data Dictionary

• Contains information describing the database

• Schema for the database

• Characteristic for each field

• Possible values for each field

• Description of the data

• Relationships

• Description of the programs

• Data Dictionary is same as catalog but may contain

wider set of information than catalog

Hierarchical model

• Data are organized in an upside down tree

• Each entity has one parent and many children

• Old and not used now

Network model

• Entities are organized in a graph

• Entities can be accessed through several paths

• Old and not used

Relational model

• Data are organized in two dimensional tables (relations)

• Tables re related to each other

• Relational Database Management System (RDBMS) are

more common model used today

Relation (Name, Attributes, Tuples)

• Relation appears in 2 dimensional table

• That doesn’t mean data stored as table; the physical

storage of data is independent of the logical

organization of data

• Attributes are the column

heading

• Each column must have a

unique heading

• Number of columns is

called the degree of the

relation

• Tuple is a collection of

attribute value

• Total number of rows

is called Cardinality of

the relation

• Each relation must have a

unique name

Name

OPERATIONS

RELATIONS

Insert operation

• Unary operation

• Insert Operation: Inserts new tuple into the relation

Delete operation

• Unary operation

• Delete Operation: Deletes tuple from the relation

Update operation

• Unary operation

• Update Operation: Changes the values of some attributes

of a tulpe

Select operation

• Unary operation

• Select Operation: Uses some criteria to select

some tuples from the original relation

Project operation

• Unary operation

• Project Operation: Creates relation in

which each tulpe has fewer attributes

Join operation

• Binary operation

• Join Operation: Takes two relation and combine them

based on common attribute

Union operation

• Binary operation

• Union Operation: Creates new relation in which each tuple

is either in the first relation, the second relation or in both

Intersection operation

• Binary operation

• Intersection Operation: Creates new relation in

which each tuple is either in both relations.

Difference operation

• Binary Operation

• Difference Operation: Creates new relation where the

new tuples are in the first relation but not in the second.

STRUCTURED

QUERY

LANGUAGE

SQL

• SQL is the standard language used for relational databases.

• It is declarative language where users declare what they want without

having to write a step by stem procedure.

• It was first implemented by Oracle Corporation

1. Insert

• SQL Insert Operation format

insert into RELATION-NAME

values (…, …, …)

Insert (Example)

insert into COURSES

values (“CIS52”, “TCP/IP Protocols”, 6)

2. Delete

• SQL Delete Operation format

delete from RELATION-NAME

where criteria

Delete (Example)

Delete from COURSES

where No = “CIS19”

3. Update

• SQL Update Operation format

update RELATION-NAME

set attribute1 = value1 attribute 2 = value2 …

where criteria

Update (Example)

update COURSES

set unit = 6

where No = “CIS51”

4. Select

• SQL Select Operation format

select *

from RELATION-NAME

where criteria

Select (Example)

select *

from COURSES

where Unit = 5

5. Project

• SQL Project Operation format

select attribute-list

from RELATION-NAME

Project (Example)

select No, Unit

from COURSES

6. Join

• SQL Join Operation format

select attribute-list

from RELATION NO1, RELATION NO2

where criteria

Join (Example)

select No, Course-Name, Unit, Professor

from COURSES, TAUGHT-BY

where COURSES.No = TAUGHT-BY.No;

7. Union

• SQL Union Operation format

select *

from RELATION NO1

union

select *

from RELATION NO2

Union (Example)

select *

from CIS15-Roster

union

select *

from CIS52-Roster;

8. Intersection

• SQL Intersection Operation format

select *

from RELATION NO1

intersection

select *

from RELATION NO2

Intersection

(Example)

• select *

• from CIS15-Roster

• intersection

• select *

• from CIS52-Roster;

9. Difference

• SQL Difference Operation format

select *

from RELATION NO1

minus

select *

from RELATION NO2

Intersection

(Example)

• select *

• from CIS15-Roster

• minus

• select *

• from CIS52-Roster;

OTHER

DATABASE

MODELS

Distributed Databases

Based on the

relational model

Data are stored in

several computers

that communicate

through the internet

Data are either (1)

fragmented or (2)

replicated

1. Fragmented

Distributed

Databases

• Data are localized; data are stored on local site

• Data can be accessed from different sites as

well as the local site

• Although each site has complete control over its

local data, there is global control through the

internet

2. Replicated Distributed Database

Each site holds an

exact replica of

another site

Any modification in

one site is repeated

at every site

If the system fails in

one site, the users at

this site can access

data at another site

The levels of Data

One or

more tables

Database

A collection of Records

Table

(relation)

group of related fields

Record

One

or more character

Field

At least

8 bits

Character

or 1

Bit

Types of Database Program

Are software tools

used to create

database. Examples of

database programs are:

File Management

Programs

Database Management

System

Advanced Databases

• Data Warehouses and Data Mining

A combination of smaller database that belong to the corporation

• Client/Server Database Systems

Software programs used to respond to remote users’ request for

information over the LAN. Server software runs on the server. Client

software connects the user to server. The front end of the software is

the part that interact with users. The back end refers to the program

code. To request information query language is used.

• Web-database integration

Enables customers to proceed enquires over the net. Users don’t have

to use SQL. The web server accept the request and translates it to a

query that is sent to the database over the internet

5 Characteristics of Good Database

ENSURING DATA IS VALIDDATA INTEGRITY

Data is separated from softwareData Independence

Repetition of input data is avoided

Avoiding data Redundancy

Data is not accessible to unauthorized usersData Security

Set procedures for adding ,deleting … records for the purpose of

optimization

Data Maintenance